package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Student;
import util.ConnectionUtil;

public class StudentDao {
	/**
	 * 查询学生列表
	 */
	public static ArrayList<Student> queryStudentList() {
		ArrayList<Student> studentList = new ArrayList<>();

		Connection conn = ConnectionUtil.getConnection();
		Statement stmt = null;

		try {

			// 执行查询
			System.out.println(" 实例化Statement对象...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT  StudentID,Name, age,classname FROM student,class  where student.classid = class.classid";
			ResultSet rs = stmt.executeQuery(sql);

			// 展开结果集数据库
			while (rs.next()) {
				// 通过字段检索
				int id = rs.getInt("StudentID");
				String name = rs.getString("Name");
				String age = rs.getString("age");
				String classname = rs.getString("classname");

				Student student = new Student(id, name, age, classname);
				studentList.add(student);
				// 输出数据

			}
			// 完成后关闭
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException se) {
			// 处理 JDBC 错误
			se.printStackTrace();
		} catch (Exception e) {
			// 处理 Class.forName 错误
			e.printStackTrace();
		}
		System.out.println("查询成功！");
		return studentList;
	}

	public static void addUseStatement(Student student) {
		Connection connection = ConnectionUtil.getConnection();
		try {
			Statement statement = connection.createStatement();
			int class_id = ClassDao.getClassId(student.getClassname());
			String sql = "INSERT INTO student VALUES(" + student.getId() + ",'" + student.getName() + "',"
					+ student.getAge() + "," + student.getCourse() + " ," + class_id + ");";
			System.err.println(sql);
			statement.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void addUsePrepareStatement(Student student) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "INSERT INTO student values(?,?,?,?,?)";
		try {
			PreparedStatement psta = conn.prepareStatement(sql);
			psta.setInt(1, student.getId());
			psta.setString(2, student.getName());
			psta.setString(3, student.getAge());
			psta.setString(4, student.getCourse());
			int class_id = ClassDao.getClassId(student.getClassname());
			psta.setInt(5, class_id);

			int result = psta.executeUpdate();
			if (result > 0) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static void del(String name) {

		Connection conn = ConnectionUtil.getConnection();

		String sql = "DELETE FROM student WHERE Name ='" + name + "'";
		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if (result > 0) {
				System.out.println("删除成功");

			} else {
				System.out.println("删除失败");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void update(String studentname, String value, int column) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = null;
		switch (column) {
		case 1:
			sql = "UPDATE student SET Name ='" + value + "' WHERE Name='" + studentname + "'";
			break;
		case 2:
			sql = "UPDATE student SET age=" + value + " WHERE Name='" + studentname + "'";
			break;
		case 3:
			sql = "UPDATE student SET CoureseID ='" + value + "' WHERE Name='" + studentname + "'";
			break;
		case 4:
			sql = "UPDATE student SET ClassID =" + value + " WHERE Name='" + studentname + "'";
			break;
		default:
			break;
		}
		Statement stat;
		try {
			stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if (result > 0) {
				System.out.println("更新成功");
			} else {
				System.out.println("更新失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static int getStuIdWithName(String stu_name) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "SELECT StudentID FROM student WHERE Name ='" + stu_name + "'";
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			if (rs.next()) {
				int stu_id = rs.getInt(1);
				return stu_id;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 110;
	}

}
